****************************************************
* Nomenclatures
****************************************************
* Labels NAFrev2
insheet using "${source}.Nomenclature\int_courts_naf_rev_2.csv", delim(",") case names clear
rename IntitulsdelaNAFrv2versionfinale APElib
keep if length(Code)>1
replace Code = subinstr(Code, ".", "",.) 
duplicates drop Code, force
keep Code APElib
save "${file}APElibel.dta", replace

* Labels English NAFrev2
insheet using "${source}.Nomenclature\Nomenclatures-NAF-rev-2_anglais.csv", delim(",") case names clear
rename intitulésdelaNAFrév2anglais APElibeng
keep if length(code)>1
gen Code = subinstr(code, ".", "",.) 
replace Code = subinstr(Code, "SECTION ", "",.) 
duplicates drop Code, force
keep Code APElibeng
merge 1:1 Code using "${file}APElibel.dta"
drop _m
order Code APElibeng APElib
format %30s APElibeng APElib
save "${file}APElibel-eng.dta", replace


* Levels NAFrev2
import delimited using "${source}.Nomenclature\naf2008_5_niveaux.csv", stringcols(_all) clear //case name
replace niv5=subinstr(niv5,".","",.)
replace niv4=subinstr(niv4,".","",.)
replace niv3=subinstr(niv3,".","",.)
replace niv2=subinstr(niv2,".","",.)
duplicates drop niv5, force
label var niv5 "APE rev2 niv5"
label var niv4 "APE rev2 niv4"
label var niv3 "APE rev2 niv3"
label var niv2 "APE rev2 niv2"
label var niv1 "APE rev2 niv1"
save "${file}nafniveauxrev2.dta", replace 

* Levels NAFrev1
import delimited using "${source}.Nomenclature\naf2003_n1-5.csv", stringcols(_all) clear //case name
rename (n_700 n_220 n_60 n_31 n_17) (niv5 niv4 niv3 niv2 niv1)
replace niv5=subinstr(niv5,".","",.)
replace niv4=subinstr(niv4,".","",.)
replace niv3=subinstr(niv3,".","",.)
replace niv2=subinstr(niv2,".","",.)
duplicates drop niv5, force
label var niv5 "APE rev1 niv5"
label var niv4 "APE rev1 niv4"
label var niv3 "APE rev1 niv3"
label var niv2 "APE rev1 niv2"
label var niv1 "APE rev1 niv1"
save "${file}nafniveauxrev1.dta", replace 


* Table passage NAFrev1 NAFrev2
import excel using "${source}.Nomenclature\table_NAF2-NAF1.xls", sheet("table NAF 2 - NAF 1 07.2008") firstrow clear
rename (NAFrév2 NAFrév1) (NAFrv2 NAFrv1)
keep NAFrv2 NAFrv1
replace NAFrv2=subinstr(NAFrv2,".","",.)
replace NAFrv1=subinstr(NAFrv1,".","",.)
replace NAFrv1=subinstr(NAFrv1,"p","",.)
replace NAFrv2=subinstr(NAFrv2,"p","",.)
rename (NAFrv2 NAFrv1) (NAF2 NAF1)
duplicates drop NAF1, force
save "${file}tableNAF2-NAF1.dta", replace 

* Table passage NAFrev1 NAFrev2
import excel using "${source}.Nomenclature\table_NAF1-NAF2.xls", sheet("table NAF 1 - NAF 2 07.2008") firstrow clear
rename (NAFrév2 NAFrév1) (NAFrv2 NAFrv1) 
keep NAFrv2 NAFrv1
replace NAFrv2=subinstr(NAFrv2,".","",.)
replace NAFrv1=subinstr(NAFrv1,".","",.)
replace NAFrv1=subinstr(NAFrv1,"p","",.)
replace NAFrv2=subinstr(NAFrv2,"p","",.)
rename (NAFrv2 NAFrv1) (NAF2 NAF1)
duplicates drop NAF1 NAF2, force
duplicates drop NAF1 , force // attention hypothese
rename (NAF1 NAF2) (APE1 APE2)
save "${file}ape_rev12.dta", replace 

* Innovative sectors -- 86 sectors NAF5
insheet using "${source}.Nomenclature\INOVA.csv", delim(",") case names clear
rename (Column1 Column2) (APE APEY)
drop if APE==""

// convert APE1 to APE2
gen APE1= APE if length(APE)==4
gen APE2= APE if length(APE)==5
replace APE1=APE2 if APE1==""
drop if APE1==""
merge 1:1 APE1 using "${file}ape_rev12.dta", update
drop if _m==2
keep APE1 APE2

// merge levels NAF
gen niv5=APE2
merge m:1 niv5 using "${file}nafniveauxrev2.dta"
drop if _merge==2
drop _merge
rename (niv1 niv2 niv3 niv4 niv5) (NAF1 NAF2 NAF3 NAF4 NAF5)

unique APE2
replace APE1= "" if length(APE1)==5
unique APE1 // 42 sectors
unique APE1 APE2
duplicates drop APE2 APE1, force
sort APE2
bys APE2: gen n=_n
drop if APE1==""&n>1

// merge industry titles at different SIC levels
gen Code=NAF5
merge m:1 Code using "${file}APElibel-eng.dta" 
rename (APElib APElibeng) (NAF5_lablfr NAF5_lablen)
drop if _merge==2
drop _merge

replace Code=NAF4
merge m:1 Code using "${file}APElibel-eng.dta" 
rename (APElib APElibeng) (NAF4_lablfr NAF4_lablen)
drop if _merge==2
drop _merge

replace Code=NAF2
merge m:1 Code using "${file}APElibel-eng.dta" 
rename (APElib APElibeng) (NAF2_lablfr NAF2_lablen)
drop if _merge==2
drop _merge
list NAF2_lablen

keep APE2 NAF5_lablfr NAF5_lablen
rename (NAF5_lablfr NAF5_lablen) (APE2_inov_lablfr APE2_inov_lablen)
duplicates drop APE2, force
save "${file}INOVA.dta", replace 

